﻿Public Class perLogradouro
    Inherits PersistenciaPadrao.AcessoBD

#Region "Transação"

    Public Sub configurarAberturaConexao()

        If perConexao.SqlComando Is Nothing OrElse perConexao.SqlConexao Is Nothing Then
            MyBase.abrirConexao()
            perConexao.SqlComando = MyBase.retornarComandoSqlServer
            perConexao.SqlConexao = MyBase.retornarConexaoSqlServer
        Else
            MyBase.configurarTransacaoSqlServer(perConexao.SqlComando, perConexao.SqlConexao)
        End If

    End Sub

    Public Sub configurarFechamentoConexao()
        MyBase.fecharConexao()
        perConexao.SqlComando = Nothing
        perConexao.SqlConexao = Nothing
    End Sub

#End Region

#Region "Métodos"

    Public Function selecionarEnderecoPorLogradouro(ByVal sLogradouro As String, ByVal iCodCidade As Int32) As DataTable
        Dim sSql As String

        sSql = "SELECT codLogradouro, descTipo, logradouro,bairro,cidade, sigla, codUf,codCidade, cep  " & Environment.NewLine &
               "  FROM endereco_completo" & Environment.NewLine &
               " WHERE logradouro like " & "'%" & sLogradouro & "%'" & Environment.NewLine &
               "   AND codCidade = @codCidade"


        MyBase.limparParametros()
        MyBase.inserirParametro("@codCidade", iCodCidade)

        Return MyBase.executarConsulta(sSql, True)

    End Function

    Public Function selecionarLogradouro() As DataTable

        Dim sSql As String = String.Empty

        sSql = " SELECT codLogradouro, logradouro,bairro,cidade, sigla" & Environment.NewLine &
               " FROM endereco_completo "

        MyBase.limparParametros()

        Return MyBase.executarConsulta(sSql, True)

    End Function

    Public Function selecionarLogradouro(ByVal iCodUF As Int32,
                                         ByVal iCodCidade As Int32,
                                         ByVal iCodBairro As Int32,
                                         ByVal sLogradouro As String) As DataTable

        Dim sSql As String = String.Empty

        sSql = " SELECT " & Environment.NewLine &
               "    codLogradouro, " & Environment.NewLine &
               "    logradouro, " & Environment.NewLine &
               "    cep, " & Environment.NewLine &
               "    codBairro, " & Environment.NewLine &
               "    bairro, " & Environment.NewLine &
               "    codCidade, " & Environment.NewLine &
               "    cidade," & Environment.NewLine &
               "    codUF, " & Environment.NewLine &
               "    sigla " & Environment.NewLine &
               " FROM endereco_completo " & Environment.NewLine &
               " WHERE 1 = 1 "

        MyBase.limparParametros()

        If iCodUF > 0 Then
            sSql &= " AND codUF = @codUF"
            MyBase.inserirParametro("@codUF", iCodUF)
        End If

        If iCodCidade > 0 Then
            sSql &= " AND codCidade = @codCidade"
            MyBase.inserirParametro("@codCidade", iCodCidade)
        End If

        If iCodBairro > 0 Then
            sSql &= " AND codBairro = @codBairro"
            MyBase.inserirParametro("@codBairro", iCodBairro)
        End If

        If Not String.IsNullOrWhiteSpace(sLogradouro) Then
            sSql &= " AND logradouro = @logradouro"
            MyBase.inserirParametro("@logradouro", sLogradouro)
        End If

        Return MyBase.executarConsulta(sSql, True)

    End Function

    Public Function selecionarLogradouro(ByVal iCodLogradouro As Int32) As DataRow

        Dim sSql As String
        Dim dtbLogradouros As DataTable

        sSql = " SELECT cep, codUf, codCidade, codLogradouro, descTipo, logradouro, bairro" & Environment.NewLine &
               " FROM endereco_completo" & Environment.NewLine &
               " WHERE codLogradouro = @codLogradouro"

        MyBase.limparParametros()
        MyBase.inserirParametro("codLogradouro", iCodLogradouro)

        dtbLogradouros = MyBase.executarConsulta(sSql, True)

        If dtbLogradouros.Rows.Count > 0 Then
            Return dtbLogradouros.Rows(0)
        Else
            Return Nothing
        End If

    End Function

    Public Function selecionarLogradouroCodigo(ByVal iCodLogradouro As Int32) As DataRow

        Dim sSql As String
        Dim dtbLogradouros As DataTable

        sSql = " SELECT " & Environment.NewLine &
               "    end_tipo.codTipo, " & Environment.NewLine &
               "    end_tipo.descTipo, " & Environment.NewLine &
               "    end_logradouros.codLogradouro, " & Environment.NewLine &
               "    end_logradouros.descLogradouro, " & Environment.NewLine &
               "    end_logradouro_cep.cep, " & Environment.NewLine &
               "    end_estado.codEstado, " & Environment.NewLine &
               "    end_cidades.codCidade, " & Environment.NewLine &
               "    end_cidades.descCidade, " & Environment.NewLine &
               "    end_bairros.codBairro, " & Environment.NewLine &
               "    end_bairros.descBairro " & Environment.NewLine &
               " FROM " & Environment.NewLine &
               "    end_logradouros " & Environment.NewLine &
               " INNER JOIN end_logradouro_cep ON end_logradouros.codLogradouro = end_logradouro_cep.codLogradouro " & Environment.NewLine &
               " INNER JOIN end_bairros ON end_logradouros.codBairro = end_bairros.codBairro " & Environment.NewLine &
               " INNER JOIN end_cidades ON end_bairros.codCidade = end_cidades.codCidade " & Environment.NewLine &
               " INNER JOIN end_estado ON end_cidades.codEstado = end_estado.codEstado " & Environment.NewLine &
               " INNER JOIN end_tipo ON end_logradouros.codTipo = end_tipo.codTipo " & Environment.NewLine &
               " WHERE " & Environment.NewLine &
               "    end_logradouros.codLogradouro = @codLogradouro "

        MyBase.limparParametros()
        MyBase.inserirParametro("codLogradouro", iCodLogradouro)

        dtbLogradouros = MyBase.executarConsulta(sSql, True)

        If dtbLogradouros.Rows.Count > 0 Then
            Return dtbLogradouros.Rows(0)
        Else
            Return Nothing
        End If

    End Function

    Public Function inserirLogradouro(ByVal iCodBairro As Int32,
                                      ByVal sLogradouro As String,
                                      ByVal iCodTipoLogradouro As Int32,
                                      ByVal bAbrirFecharConexao As Boolean) As Int32
        Dim sSql As String

        If Not bAbrirFecharConexao Then
            Me.configurarAberturaConexao()
        End If

        sSql = " INSERT INTO end_logradouros " & Environment.NewLine &
               " ( " & Environment.NewLine &
               "    codBairro, " & Environment.NewLine &
               "    descLogradouro, " & Environment.NewLine &
               "    codTipo " & Environment.NewLine &
               " )" & Environment.NewLine &
               " VALUES" & Environment.NewLine &
               " (" & Environment.NewLine &
               "    @codBairro, " & Environment.NewLine &
               "    @descLogradouro, " & Environment.NewLine &
               "    @codTipo " & Environment.NewLine &
               " ); SELECT SCOPE_IDENTITY()"

        MyBase.limparParametros()
        MyBase.inserirParametro("@codBairro", iCodBairro)
        MyBase.inserirParametro("@descLogradouro", sLogradouro)
        MyBase.inserirParametro("@codTipo", iCodTipoLogradouro)

        Return MyBase.executarConsultaCampo(sSql, bAbrirFecharConexao)

    End Function

    Public Sub inserirLogradouroCEP(ByVal iCodLogradouro As Int32,
                                    ByVal iCEP As Int32,
                                    ByVal bAbrirFecharConexao As Boolean)
        Dim sSql As String

        If Not bAbrirFecharConexao Then
            Me.configurarAberturaConexao()
        End If

        sSql = " INSERT INTO end_logradouro_cep " & Environment.NewLine &
               " ( " & Environment.NewLine &
               "    codLogradouro, " & Environment.NewLine &
               "    cep " & Environment.NewLine &
               " )" & Environment.NewLine &
               " VALUES" & Environment.NewLine &
               " (" & Environment.NewLine &
               "    @codLogradouro, " & Environment.NewLine &
               "    @cep " & Environment.NewLine &
               " ); SELECT SCOPE_IDENTITY()"

        MyBase.limparParametros()
        MyBase.inserirParametro("@codLogradouro", iCodLogradouro)
        MyBase.inserirParametro("@cep", iCEP)

        MyBase.executarAcao(sSql, bAbrirFecharConexao)

    End Sub

    Public Sub editarLogradouro(ByVal iCodLogradouro As Int32,
                                ByVal iCodBairro As Int32,
                                ByVal sLogradouro As String,
                                ByVal iCodTipoLogradouro As Int32,
                                ByVal bAbrirFecharConexao As Boolean)

        Dim sSql As String

        sSql = " UPDATE end_logradouros SET " & Environment.NewLine &
               "    codBairro = @codBairro, " & Environment.NewLine &
               "    descLogradouro = @descLogradouro " & Environment.NewLine &
               "    codTipo = @codTipo " & Environment.NewLine &
               " WHERE codLogradouro = @codLogradouro "

        MyBase.limparParametros()
        MyBase.inserirParametro("@codBairro", iCodBairro)
        MyBase.inserirParametro("@descLogradouro", sLogradouro)
        MyBase.inserirParametro("@codTipo", iCodTipoLogradouro)
        MyBase.inserirParametro("@codLogradouro", iCodLogradouro)

        MyBase.executarAcao(sSql, bAbrirFecharConexao)

    End Sub

    Public Sub excluirLogradouro(ByVal iCodLogradouro As Int32)

        Dim sSql As String

        sSql = " DELETE FROM " & Environment.NewLine &
               "    end_logradouros " & Environment.NewLine &
               " WHERE " & Environment.NewLine &
               "    codLogradouro = @codLogradouro; "

        MyBase.limparParametros()
        MyBase.inserirParametro("@codLogradouro", iCodLogradouro)

        MyBase.executarAcao(sSql, True)

    End Sub

    Public Sub excluirLogradouroCEP(ByVal iCodLogradouro As Int32,
                                    ByVal bAbrirFechar As Boolean)

        Dim sSql As String

        If Not bAbrirFechar Then
            Me.configurarAberturaConexao()
        End If

        sSql = " DELETE FROM " & Environment.NewLine &
               "    end_logradouro_cep " & Environment.NewLine &
               " WHERE " & Environment.NewLine &
               "    codLogradouro = @codLogradouro; "

        MyBase.limparParametros()
        MyBase.inserirParametro("@codLogradouro", iCodLogradouro)

        MyBase.executarAcao(sSql, bAbrirFechar)

    End Sub

    Public Function validarLogradouro(ByVal iCodLogradouro As Int32,
                                      ByVal sLogradouro As String,
                                      ByVal iCodTipo As Int32) As Boolean

        Dim sSql As String

        sSql = " SELECT " & Environment.NewLine &
               "     end_logradouros.* " &
               " FROM  " & Environment.NewLine &
               "     end_logradouros " & Environment.NewLine &
               " WHERE " & Environment.NewLine &
               "     descLogradouro = @descLogradouro " & Environment.NewLine &
               " AND codTipo = @codTipo " & Environment.NewLine &
               " AND codLogradouro <> @codLogradouro "

        MyBase.limparParametros()
        MyBase.inserirParametro("@descLogradouro", sLogradouro)
        MyBase.inserirParametro("@codTipo", iCodTipo)
        MyBase.inserirParametro("@codLogradouro", iCodLogradouro)

        Return MyBase.executarConsulta(sSql, True).Rows.Count = 0

    End Function

#End Region

End Class

